package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Like;
import com.wanmait.spacesys.vo.Post;
import com.wanmait.spacesys.vo.Userinfo;

public class PostDAO {
	
	//分页
	public int getRecordCount() {
		int count = 0;
		String sql = "select count(id) from post";
		Connection conn=DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	
	public void insert(Post post) {//发布文章
		String sql="INSERT INTO post(title,content,userinfo_id) "
				+ "VALUES (?,?,?)";
		DbHelper.update(sql, post.getTitle(),
				post.getContent(),
				post.getUserinfo().getId());
	}
	//查找被点赞用户
	public int praisedUserinfo(Integer id) {
		int count = 0;
		String sql = "SELECT post.id,userinfo.id "
				+ "FROM post "
				+ "INNER JOIN userinfo ON post.userinfo_id=userinfo.id "
				+ "WHERE post.id="+id;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt("userinfo.id");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	//点赞功能 id是帖子id guestId是点赞人的Id userId是被点赞人的Id
	public void praised(Integer id,Integer guestId,Integer userId) {  
		String sql = "INSERT INTO `like` (post_id,guest_id,userinfo_id) VALUES (?,?,?)";
		DbHelper.update(sql,id,guestId,userId);
		String sqltwo = "UPDATE post SET praised=praised+1 WHERE id=?";
		DbHelper.update(sqltwo, id);
		String sqlThree = "UPDATE userinfo SET praise=praise+1 WHERE userinfo.id=?";
		DbHelper.update(sqlThree, userId);
	}
	//查找点赞表的Id 取消点赞会用
	public int cancelLikeId(Integer id,Integer guestId,Integer userId) {
		int likeId = 0;
		String sql = "SELECT `like`.id "
				+ "FROM `like` "
				+ "WHERE post_id="+id+" AND userinfo_id="+userId+" AND guest_id="+guestId;
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			likeId = rs.getInt("id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return likeId;
	}
	//取消点赞 likeId是帖子like表里的Id id是帖子的Id userId是被点赞人的Id
	public void cancelLike(Integer likeId,Integer id,Integer userId) {
		String sql = "DELETE FROM `like` "
				+ "WHERE id=?";
		DbHelper.update(sql, likeId);
		String sqltwo = "UPDATE post SET praised=praised-1 WHERE id=?";
		DbHelper.update(sqltwo, id);
		String sqlThree = "UPDATE userinfo SET praise=praise-1 WHERE userinfo.id=?";
		DbHelper.update(sqlThree, userId);
				
	}
	//搜索框
	public List<Post> search(String text){
		String sql = "SELECT * FROM post "
				+ "LEFT JOIN userinfo ON post.userinfo_id=userinfo.id "
				+ "WHERE title like ? ";
		List<Post> texts = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,"%"+text+"%%");
			rs = ps.executeQuery();
			if(rs.next()) {
				while(rs.next()) {
					Post post = new Post();
					post.setId(rs.getInt("id"));
					post.setTitle(rs.getString("title"));
					post.setContent(rs.getString("content"));
					post.setPubTime(rs.getTimestamp("pub_time"));
					post.setPraised(rs.getInt("praised"));
					post.setCollected(rs.getInt("collected"));
					
					Userinfo userinfo = new Userinfo();
					userinfo.setNickname(rs.getString("nickname"));
					userinfo.setId(rs.getInt("userinfo.id"));
					post.setUserinfo(userinfo);

					texts.add(post);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		
		return texts;
	}
	//在single.jsp随机生成5篇帖子
	public List<Post> randomList(){
		String sql = "SELECT post.id,title,userinfo.nickname,pub_time,"
				+ "userinfo.id,praised  "
				+ "FROM post "
				+ "LEFT JOIN userinfo ON post.userinfo_id=userinfo.id "
				+ "ORDER BY RAND() "
				+ "LIMIT 0,5 ";
		List<Post> posts = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Post post = new Post();
				post.setId(rs.getInt("id"));
				post.setTitle(rs.getString("title"));
				post.setPubTime(rs.getTimestamp("pub_time"));
				post.setPraised(rs.getInt("praised"));
				
				
				Userinfo userinfo = new Userinfo();
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setId(rs.getInt("userinfo.id"));
				post.setUserinfo(userinfo);
				
				posts.add(post);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		
		return posts;
	}
	//最新文章列表
	public List<Post> newFindAll(int pageNum){	
		String sql="SELECT post.id,title,content,userinfo.nickname,pub_time,"
				+ "userinfo.id,praised,collected "
				+ "FROM post "
				+ "LEFT JOIN userinfo ON post.userinfo_id=userinfo.id "
				+ "where ispass=1 "
				+ "ORDER BY pub_time desc "
				+ "LIMIT "+(pageNum-1)*8+",8";
		List<Post> posts = new ArrayList<>();
		Connection conn=DbHelper.getConnection();
		PreparedStatement ps=null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Post post = new Post();
				post.setId(rs.getInt("id"));
				post.setTitle(rs.getString("title"));
				post.setContent(rs.getString("content"));
				post.setPubTime(rs.getTimestamp("pub_time"));
				post.setPraised(rs.getInt("praised"));
				post.setCollected(rs.getInt("collected"));
				
				Userinfo userinfo = new Userinfo();
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setId(rs.getInt("userinfo.id"));
				post.setUserinfo(userinfo);
				
				posts.add(post);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return posts;
	}
	//根据id去到single.jsp 帖子详情页
	public Post findById(Integer id) {
		Post post = null;
		String sql = "SELECT post.id,title,content,userinfo.nickname,userinfo.id,pub_time,praised,collected "
				+ "FROM post "
				+ "LEFT JOIN userinfo ON post.userinfo_id=userinfo.id "
				+ "WHERE post.id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				post = new Post();
				post.setId(rs.getInt("id"));
				post.setTitle(rs.getString("title"));
				post.setContent(rs.getString("content"));
				post.setPubTime(rs.getTimestamp("pub_time"));
				post.setPraised(rs.getInt("praised"));
				post.setCollected(rs.getInt("collected"));
				
				Userinfo userinfo = new Userinfo();
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setId(rs.getInt("userinfo.id"));
				post.setUserinfo(userinfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return post;
	}
	//最热文章列表
	public List<Post> hotFindAll(int pageNum) { 
		String sql = "SELECT post.id,title,content,userinfo.nickname,pub_time,"
				+ "userinfo.id,praised,collected,ispass "
				+ "FROM post "
				+ "LEFT JOIN userinfo ON post.userinfo_id=userinfo.id "
				+ "where ispass=1 "
				+ "ORDER BY praised desc "
				+ "LIMIT "+(pageNum-1)*8+",8";
		List<Post> posts = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Post post = new Post();
				post.setId(rs.getInt("id"));
				post.setTitle(rs.getString("title"));
				post.setContent(rs.getString("content"));
				post.setPubTime(rs.getTimestamp("pub_time"));
				post.setPraised(rs.getInt("praised"));
				post.setCollected(rs.getInt("collected"));
				
				Userinfo userinfo = new Userinfo();
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setId(rs.getInt("userinfo.id"));
				post.setUserinfo(userinfo);
				
				posts.add(post);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return posts;
	}
	
	

	//查出ispass是1的 已经通过的帖子
	public List<Post> adopt(Integer userinfoId,int pageNum){
	
		  String sql="select * from post where ispass=1 and enable=1 and post.userinfo_id =? "
				  + "limit "+(pageNum-1)*2+",2";
		  List<Post> post = new ArrayList<>();
		  Connection conn = DbHelper.getConnection();
		  PreparedStatement ps = null;
		  ResultSet rs = null;
		  try {
			ps=conn.prepareStatement(sql);	
			ps.setInt(1,userinfoId );
			rs = ps.executeQuery();
			while(rs.next())
			{
				Post pos = new Post();
				pos.setId(rs.getInt("id"));
				pos.setTitle(rs.getString("title"));
				pos.setPraised(rs.getInt("praised"));
				pos.setPubTime(rs.getTimestamp("pub_time"));
				pos.setIspass(rs.getBoolean("ispass"));
				pos.setEnable(rs.getBoolean("enable"));
				post.add(pos);
				
				Userinfo userinfo=new Userinfo();
				userinfo.setId(rs.getInt("userinfo_id"));
				pos.setUserinfo(userinfo);
				post.add(pos);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		  finally {
			  DbHelper.close(rs, ps, conn);
			}
		return post;		  
	}
	
	
	
	
	
	
	
	//查出ispass是2的 已经通过的帖子
	public Post findPostId(Integer userinfoId,Integer postId){
	
		  String sql="select * from post where ispass=2 and enable=1 and post.userinfo_id =? AND id=?";
		  Post pos=null;
		  Connection conn = DbHelper.getConnection();
		  PreparedStatement ps = null;
		  ResultSet rs = null;
		  try {
			ps=conn.prepareStatement(sql);	
			ps.setInt(1,userinfoId );
			ps.setInt(2, postId);
			rs = ps.executeQuery();
			if(rs.next())
			{
				pos = new Post();
				pos.setId(rs.getInt("id"));
				pos.setTitle(rs.getString("title"));
				pos.setPraised(rs.getInt("praised"));
				pos.setPubTime(rs.getTimestamp("pub_time"));
				pos.setIspass(rs.getBoolean("ispass"));
				pos.setEnable(rs.getBoolean("enable"));
				Userinfo userinfo=new Userinfo();
				userinfo.setId(rs.getInt("userinfo_id"));
				pos.setUserinfo(userinfo);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
		  finally {
			  DbHelper.close(rs, ps, conn);
			}
		return pos;		  
	}
	
	
	
	
	
	
	
	//真删除
	public void delete(Integer id) {
		String sql = "delete from post where id=?";
		DbHelper.update(sql,id);
		
	}
	
	
	public void plusdelete(Integer id) {
		
		String sql = "update post set enable=0 where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
		    ps.setInt(1, id);
		    ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps, conn);
		}
	}
	
	//查出ispass是2的 未通过的帖子
	public List<Post> list( Integer userinfoId ,int pageNum)
	{
		 String sql= "select * from post where ispass=2 and enable=1 and post.userinfo_id =? "
				 + "limit "+(pageNum-1)*2+",2";
		 List<Post> posts = new ArrayList<>();
		 Connection conn = DbHelper.getConnection();
		 PreparedStatement ps = null;
		 ResultSet rs =null;
		 try {
			 ps=conn.prepareStatement(sql);
			 ps.setInt(1, userinfoId);
			 rs = ps.executeQuery();
			while(rs.next())
			{
				Post poss = new Post();
				poss.setId(rs.getInt("id"));
				poss.setTitle(rs.getString("title"));
				poss.setPraised(rs.getInt("praised"));
				poss.setPubTime(rs.getTimestamp("pub_time"));
				poss.setIspass(rs.getBoolean("ispass"));
				poss.setEnable(rs.getBoolean("enable"));
				Userinfo userinfo=new Userinfo();
				userinfo.setId(rs.getInt("userinfo_id"));
				poss.setUserinfo(userinfo);
				posts.add(poss);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally
		 {
			 DbHelper.close(rs, ps, conn);
		 }
		 return posts;
	}	
	
	
	
	
	
	
	
	
	//未通过帖子修改
	// 修改用户帖子
	public Post postmodify(Integer id ,Integer userinfoid)
	{
		 Post Post = null;
		 List<Post> postt=new ArrayList<Post>();
		 String sql="select * from post where id=? and post.userinfo_id=?";
		 Connection conn = DbHelper.getConnection();
		 PreparedStatement ps = null;
		 ResultSet rs =null;
		 
		 try {	 
		 ps=conn.prepareStatement(sql);
		 ps.setInt(1,id);
		 ps.setInt(2, userinfoid);
		 rs = ps.executeQuery();
		 while(rs.next())
		 {
		 
		 Post post = new Post();
		 post.setId(rs.getInt("id"));
		 post.setTitle(rs.getString("title"));
		 post.setContent(rs.getString("content"));
		 post.setUserinfoId(rs.getInt("userinfo_id"));
		 
		 Userinfo userinfo = new Userinfo();
		 userinfo.setId(rs.getInt("id"));
		 //userinfo.setUsername(rs.getString("username"));
		 post.setUserinfo(userinfo);
		 postt.add(post);
		 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally
		 {
			 DbHelper.close(rs, ps, conn);
		 }
		 return Post;
	}
	//修改
	public void update (Post post,Integer userinfoId)
	{
		 String sql = "update post set title=? where ispass=2 and enable=1 and id=? and userinfo_id=? ";
		 Connection conn = DbHelper.getConnection();
		 PreparedStatement ps = null;
		 try {
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, post.getTitle());
			ps.setInt(2, post.getId());
			//ps.setString(3,post.getContent());
			ps.setInt(3, userinfoId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally
		 {
			 DbHelper.close( ps, conn);
		 }
		 
	}
	
	
	
	
	
	
	//查询内容
	public Post myfindAllByPostId(Integer userinfoId,Integer postId){
		List<Post> posts=new ArrayList<Post>();
		Connection connection=DbHelper.getConnection();
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		Post post=null;
		String sql="select post.id,post.title,post.pub_time,post.content,userinfo_id from post  where post.userinfo_id=? and post.id=?";	
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			preparedStatement.setInt(2, postId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
			    post=new Post();
				post.setId(resultSet.getInt("id"));
				post.setTitle(resultSet.getString("title"));
				post.setPubTime(resultSet.getTimestamp("pub_time"));
				post.setContent(resultSet.getString("content"));
				Userinfo  userinfo=new Userinfo();
				userinfo.setId(resultSet.getInt("userinfo_id"));
				/* userinfo.setUsername(resultSet.getString("username")); */
				post.setUserinfo(userinfo);
				posts.add(post);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(resultSet, preparedStatement, connection);
		}
		return post;
		

}
	
	//通过用户id统计我发布总数
  	public int getCount(Integer userinfoId) {
		int count = 0;
		String sql = "SELECT COUNT(userinfo_id) FROM post WHERE userinfo_id=?";
		Connection connection = DbHelper.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(resultSet,preparedStatement,connection);
		}
		return count;
		
	}	
	
  
/*
* public static void main(String[] args) { Post post=new Post();
* post.setTitle("国家需要，我就去做"); PostDAO.getPostDAO().update(post,3);
* 
* }
*/
	
}
